﻿	alter table dbo.PayApplyBill add 	PayState		VARCHAR(20)--付款状态，'已付款'，'未付款'，'部分付款'
	alter table dbo.PayApplyBill add 	HavePayTotalAmount	numeric(16,2) --已经付款的合计金额
	alter table dbo.PayApplyBill add 	FinalPayDate			datetime --最后付款时间

	alter table dbo.PayApplyBillDtl add InvoiceAmount numeric(16,2)--发票的票面金额

	alter table dbo.PayApplyBillHistory add 	PayState		VARCHAR(20)--付款状态，'已付款'，'未付款'，'部分付款'
	alter table dbo.PayApplyBillHistory add 	HavePayTotalAmount	numeric(16,2) --已经付款的合计金额
	alter table dbo.PayApplyBillHistory add 	FinalPayDate			datetime --最后付款时间

	alter table dbo.PayApplyBillDtlHistory add InvoiceAmount numeric(16,2)--发票的票面金额

	--重新执行TriggerPayApplyBillDtl，TriggerPayApplyBill
	--检查：
	update dbo.PayApplyBillDtl  set InvoiceAmount = (select AmountTax from dbo.InvoiceBill where InvoiceNo = dbo.PayApplyBillDtl.InvoiceNo ) where InvoiceAmount is null
	update dbo.PayApplyBillDtlHistory  set InvoiceAmount = (select AmountTax from dbo.InvoiceBill where InvoiceNo = dbo.PayApplyBillDtlHistory.InvoiceNo ) where InvoiceAmount is null
	update dbo.InStoreBill set InBillDate = BillDate where InBillDate is null
	update dbo.OutStoreBill set OutBillDate = BillDate where OutBillDate is null

--drop table PayApplyBillItem
create table PayApplyBillItem  --付款申请单付款项表---一个付款申请单可能要多次付款
(
	PayApplyBillItemID			VARCHAR(36) not null,--付款项编号
	PayApplyBillID				VARCHAR(20) not null,--付款单编号
	Idx									INT not null,
	PayAmount							numeric(16,2),   --付款金额
	PayRemark						VARCHAR(100) default '',--付款备注
	PayID								VARCHAR(50) , --付款ID
	PayName							VARCHAR(50) , --付款Name
	PayTime							datetime    default getdate(),		 --付款时间
	ItemState							VARCHAR(4) not null default '正常', --状态：正常，作废
	CancelItemDate				datetime, --作废付款项日期
	CancelID							VARCHAR(50) , --作废付款项ID
	CancelName					VARCHAR(50) , --作废付款项Name
	ExtVarChar1						VARCHAR(50) default '',
	ExtVarChar2						 VARCHAR(50) default '',
	ExtVarChar3						VARCHAR(50) default '',
	ExtVarChar4						VARCHAR(50) default '',
    ExtNumeric1						numeric(16,4),
	ExtNumeric2						numeric(16,4),
	ExtNumeric3						numeric(16,4),
	ExtNumeric4						numeric(16,4),
	primary key(PayApplyBillItemID)
)

--drop table InvoiceBillHistory
create table PayApplyBillItemHistory  --付款申请单付款项表历史
(
	AutoID								int Identity,
	PayApplyBillItemID			VARCHAR(36) not null,--付款项编号
	PayApplyBillID				VARCHAR(20) not null,--付款单编号
	Idx									INT not null,
	PayAmount							numeric(16,2),   --付款金额
	PayRemark						VARCHAR(100) default '',--付款备注
	PayID								VARCHAR(50) , --付款ID
	PayName							VARCHAR(50) , --付款Name
	PayTime							datetime    default getdate(),		 --付款时间
	ItemState							VARCHAR(4) not null default '正常', --状态：正常，作废
	CancelItemDate				datetime, --作废付款项日期
	CancelID							VARCHAR(50) , --作废付款项ID
	CancelName					VARCHAR(50) , --作废付款项Name
	ExtVarChar1						VARCHAR(50) default '',
	ExtVarChar2						 VARCHAR(50) default '',
	ExtVarChar3						VARCHAR(50) default '',
	ExtVarChar4						VARCHAR(50) default '',
    ExtNumeric1						numeric(16,4),
	ExtNumeric2						numeric(16,4),
	ExtNumeric3						numeric(16,4),
	ExtNumeric4						numeric(16,4),
	OperationTime			 datetime  default getdate(),	  
	primary key(AutoID)
)
GO

--付款申请单付款项表dbo.PayApplyBillItemHistory
CREATE TRIGGER TriggerPayApplyBillItem
   ON  PayApplyBillItem  
   AFTER UPDATE,insert
AS 
BEGIN
	insert into PayApplyBillItemHistory(PayApplyBillItemID, PayApplyBillID, Idx, PayAmount, PayRemark, PayID, PayName, PayTime, ItemState, CancelItemDate, CancelID, CancelName, ExtVarChar1, ExtVarChar2, ExtVarChar3, ExtVarChar4, ExtNumeric1, ExtNumeric2, ExtNumeric3, ExtNumeric4)
	select * from inserted;
END
GO

--执行插入代码
insert into InStoreType values('DRKC','导入库存入库','1','0');

--执行更新视图VPreviewStoreGoods，先删除，再执行

--在表Report的付款申请单主表的字段ExtVarChar1中放入值：ReportPayApplyBillAop
--在表Report的入库单主表的字段ExtVarChar1中放入值：ReportInStoreBillAop

go

